package com.hc.mapper;

import com.hc.entity.Refrigeratormv;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author LiuZhiHao
 * @since 2019-12-04
 */
public interface RefrigeratormvMapper extends BaseMapper<Refrigeratormv> {

    /* @Select("SELECT equipmentno FROM monitorequipment WHERE equipmentname = #{equipmnetname} AND hospitalcode='H0006'")
     String selNo(@Param("equipmnetname") String equipmnetname);*/
    //查询一个天里面留个冰箱的所有数据
    @Select("SELECT\n" +
            "\tinput_time,\n" +
            "\tmean_value,\n" +
            "\tequipment_name,\n" +
            "\ttime_interval\n" +
            "FROM\n" +
            "\trefrigeratormv\n" +
            "WHERE\n" +
            "\tDATE_FORMAT(input_time, '%Y-%m-%d') = DATE_FORMAT(#{date}, '%Y-%m-%d')")
    List<Refrigeratormv> selectfrigerator(@Param("date") String date);


    @Select("SELECT\n" +
            "\tinput_time,\n" +
            "\tmean_value,\n" +
            "\tequipment_name,\n" +
            "\ttime_interval\n" +
            "FROM\n" +
            "\trefrigeratormv\n" +
            "WHERE\n" +
            "\tDATE_FORMAT(input_time, '%Y-%m-%d') = DATE_FORMAT(#{date}, '%Y-%m-%d') limit 1")
    List<Refrigeratormv> selectfrigeratorLimit(@Param("date") String date);

    @Select("Delete\n" +
            "FROM\n" +
            "\trefrigeratormv\n" +
            "WHERE\n" +
            "\tDATE_FORMAT(input_time, '%Y-%m-%d') = DATE_FORMAT(#{date}, '%Y-%m-%d')")
    void deleteFrigerator(@Param("date") String date);

    //查询一个月里面一个冰箱的所有数据
    @Select("SELECT \n" +
            "equipment_name,\n" +
            "input_time,\n" +
            "mean_value,\n" +
            "time_interval\n" +
            "FROM \n" +
            "refrigeratormv \n" +
            "WHERE\n" +
            "equipment_name=#{equipmentname}\n" +
            "and\n" +
            "DATE_FORMAT(input_time,'%Y-%m-%d')\n" +
            "BETWEEN \n" +
            "DATE_FORMAT(#{startTime}, '%Y-%m-%d')\n" +
            "and \n" +
            "DATE_FORMAT(#{endTime}, '%Y-%m-%d');")
    List<Refrigeratormv> selectfrigeratormonth(@Param("equipmentname") String equipmentname, @Param("startTime") String startTime, @Param("endTime") String endTime);


    //查询所有冰箱的名称返回一个list集合
    @Select("select equipmentname from monitorequipment where equipmenttypeid=4 and hospitalcode='H0006'")
    List<String> selectFridgeSize();


    @Select("SELECT\n" +
            "\tt1.equipmentno AS equipmentNo,\n" +
            "\tt1.equipmentname AS equipmentName,\n" +
            "\tt2.currenttemperature AS meanValue1 \n" +
            "FROM\n" +
            "\tmonitorequipment t1\n" +
            "\tLEFT JOIN ${tableName} t2 ON t1.equipmentno = t2.equipmentno \n" +
            "\tAND t2.inputdatetime BETWEEN #{startTime} \n" +
            "\tAND #{endTime} \n" +
            "WHERE\n" +
            "\tt1.equipmenttypeid = '4' \n" +
            "\tAND t1.hospitalcode = 'H0006'\n")
    List<Refrigeratormv> getTimeInterval(@Param("tableName") String tableName,@Param("startTime") String startTime, @Param("endTime") String endTime);

    @Select("SELECT\n" +
            "\tt1.equipmentno AS equipmentNo,\n" +
            "\tt1.equipmentname AS equipmentName,\n" +
            "\tt2.currenttemperature AS meanValue1 \n" +
            "FROM\n" +
            "\tmonitorequipment t1\n" +
            "\tLEFT JOIN monitorequipmentlastdata_20200604 t2 ON t1.equipmentno = t2.equipmentno \n" +
            "\tAND t2.inputdatetime BETWEEN #{startTime} \n" +
            "\tAND #{endTime} \n" +
            "WHERE\n" +
            "\tt1.equipmenttypeid = '4' \n" +
            "\tAND t1.hospitalcode = 'H0006'\n")
    List<Refrigeratormv> getTimeInterval64(@Param("startTime") String startTime, @Param("endTime") String endTime);
}
